using System;
using System.Data; 
using System.Data.OleDb ; 
using System.IO;
using System.Xml;
using System.Xml.Schema;   
using System.ComponentModel;
/// <summary>
	/// Summary description for ExcelReader.
	/// </summary>
public 	class ExcelReader : IDisposable
	{		
		#region Variable Declaration
			private OleDbConnection cn;			
			private string ExcelCon = @"Provider=Microsoft.Jet.OLEDB.4.0;";
			private string strConnectionString;					
		#endregion

		#region Constructors
		/// <summary>
		/// Initialize ExcelReader component using the specified File name
		/// </summary>
		/// <param name="strFileName"></param>
		public ExcelReader(string strFileName)
		{			
			try
			{
				strConnectionString=ExcelCon+"Data Source="+strFileName+";Extended Properties="+ Convert.ToChar(34).ToString()+"Excel 8.0;HDR=Yes;IMEX=1"+Convert.ToChar(34).ToString();
				cn=new OleDbConnection(); 
				cn.ConnectionString = strConnectionString; 			
			}
			catch(Exception ex)
			{
				throw new Exception("Error in establish connection from source",ex);  
			}
		}
		#endregion

		#region Excel File Info
		/// <summary>
		/// Return array of sheet name
		/// </summary>
		/// <returns></returns>
		public String[] GetExcelSheetNames()
		{
			System.Data.DataTable dt = null;
			try
			{				
				if(cn.State==ConnectionState.Closed) cn.Open();				
				// Get the data table containing the schema				
				dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); 

				if(dt == null)
				{
					return null;
				}
				String[] excelSheets = new String[dt.Rows.Count];
				
				int i = 0;
				// Add the sheet name to the string array.
				foreach(DataRow row in dt.Rows)
				{
					string strSheetTableName = row["TABLE_NAME"].ToString();
					excelSheets[i] = strSheetTableName.Substring(0,strSheetTableName.Length-1); 
					i++;
				}

				//Dispose DataTable
				dt.Dispose();

				return excelSheets;
			}
			catch(Exception ex)
			{
				//Dispose DataTable
				//dt.Dispose();
                //System.Web.HttpContext.Current.Response.Write(ex); 

				//throw new Exception("Error in reading XLS file",ex) ;
                throw new Exception(ex.ToString(), ex);
			}
			finally
			{
				// Clean up.
				cn.Close();				
			}
		}

         
		/// <summary>
		/// Return data in DataTable of specified sheet;
		/// </summary>
		/// <param name="strSheetName"></param>
		/// <returns></returns>
		public DataTable GetDataTable(string strSheetName)
		{
            
			OleDbDataAdapter daAdapter=null;
			DataTable dt=null;

			try
			{				
				string strComand;
				string SheetName;
				string Range;			

				if(strSheetName.IndexOf("|") > 0)
				{
					SheetName = strSheetName.Substring(0,strSheetName.IndexOf("|"));
					Range = strSheetName.Substring(strSheetName.IndexOf("|")+1);
					strComand="select * from ["+SheetName+"$"+Range+"]";	
				}
				else
				{
					strComand="select * from ["+strSheetName+"$]";	
				}	
				
				daAdapter=new OleDbDataAdapter(strComand,cn);
				dt=new DataTable(strSheetName);
				//System.Web.HttpContext.Current.Response.Write(strSheetName);
				//System.Web.HttpContext.Current.Response.End();
				//filling the dataset with available data in sheet
				daAdapter.FillSchema(dt,SchemaType.Source);
				daAdapter.Fill(dt); 

				return dt ;
			}
			catch(Exception ex)
			{
                //System.Web.HttpContext.Current.Response.Write(ex); 
				//throw new Exception("Error in reading data from XLS file", ex);  
                throw new Exception(ex.ToString(), ex);
			}
			finally
			{	
				//Dispose OleDbDataAdapter 
                
				daAdapter.Dispose();

				//Dispose DataTable
				dt.Dispose();

				cn.Close();
			}
		}
		
		#endregion

		#region Dispose
		public void Dispose()
		{
			// Dispose of object.
			if (cn !=null)
			{
				cn.Dispose();
				cn=null;
			}			
			
		}
		#endregion
	}

